﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class MasterContactDAL : IDALMasterContact
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public MasterContactDAL()
        {

        }

        public IList<MasterContactInfo> GetMasterContact()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<MasterContactInfo> infoList = new List<MasterContactInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT c.*"
                            + ", g.ICM_CON_GRP_NAME"
                            + ", s.ICM_CON_STAT_NAME"
                            + ", v.ICM_VAT_NAME"
                        + " FROM ICM_MAS_CONTACT c"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP g ON c.ICM_CON_GRP_SEQ = g.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_STATUS s ON c.ICM_CON_STAT_SEQ = s.ICM_CON_STAT_SEQ"
                            + " INNER JOIN ICM_SYS_VAT v ON c.ICM_VAT_SEQ = v.ICM_VAT_SEQ" 
                        + " WHERE c.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        MasterContactInfo info = new MasterContactInfo();
                        SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                        SysContactStatusInfo infoStatus = new SysContactStatusInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_CON_SEQ"]);
                        info.Name_th = (dr[i]["ICM_CON_NAME_TH"]).ToString();
                        info.Name_eng = (dr[i]["ICM_CON_NAME_ENG"]).ToString();
                        infoGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_GRP_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_CON_GRP_SEQ"]));
                        infoGroup.Name = (dr[i]["ICM_CON_GRP_NAME"]).ToString();
                        infoStatus.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_STAT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_CON_STAT_SEQ"]));
                        infoStatus.Name = (dr[i]["ICM_CON_STAT_NAME"]).ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["ICM_VAT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_VAT_SEQ"]));
                        infoVat.Name = (dr[i]["ICM_VAT_NAME"]).ToString();
                        info.Country = (dr[i]["ICM_CON_COUNTRY"]).ToString();
                        info.Tax_code = (dr[i]["ICM_CON_TAX_CODE"]).ToString();
                        info.Gfmis_code = (dr[i]["ICM_CON_GFMIS_CODE"]).ToString();
                        info.Reg_date = (Convert.IsDBNull(dr[i]["ICM_CON_REG_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["ICM_CON_REG_DATE"]));
                        info.Reg_capital = (Convert.IsDBNull(dr[i]["ICM_CON_REG_CAPITAL"]) ? null : (Nullable<double>)(dr[i]["ICM_CON_REG_CAPITAL"]));
                        info.Status_remark = (dr[i]["ICM_CON_STAT_REMARK"]).ToString();
                        info.Coordinator = (dr[i]["ICM_CON_COORDINATOR"]).ToString();
                        info.Building = (dr[i]["ICM_CON_BUILDING"]).ToString();
                        info.Floor = (dr[i]["ICM_CON_FLOOR"]).ToString();
                        info.Village = (dr[i]["ICM_CON_VILLAGE"]).ToString();
                        info.Address = (dr[i]["ICM_CON_ADDRESS"]).ToString();
                        info.Road = (dr[i]["ICM_CON_ROAD"]).ToString();
                        info.Lane = (dr[i]["ICM_CON_LANE"]).ToString();
                        info.District = (dr[i]["ICM_CON_DISTRICT"]).ToString();
                        info.Amphur = (dr[i]["ICM_CON_AMPHUR"]).ToString();
                        info.Post = (dr[i]["ICM_CON_POST"]).ToString();
                        info.Province = (dr[i]["ICM_CON_PROVINCE"]).ToString();
                        info.Tel = (dr[i]["ICM_CON_TEL"]).ToString();
                        info.Tel_next = (dr[i]["ICM_CON_TEL_NEXT"]).ToString();
                        info.Fax = (dr[i]["ICM_CON_FAX"]).ToString();
                        info.Fax_next = (dr[i]["ICM_CON_FAX_NEXT"]).ToString();
                        info.Email = (dr[i]["ICM_CON_EMAIL"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoRecordStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoRecordStatus;
                        info.InfoGroup = infoGroup;
                        info.InfoStatus = infoStatus;
                        info.InfoVat = infoVat;
                        info.InfoRecord = infoRecord;
                        info.All_address += ((dr[i]["ICM_CON_COUNTRY"]).ToString().Length > 0 ? " ประเทศ" + (dr[i]["ICM_CON_COUNTRY"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_BUILDING"]).ToString().Length > 0 ? " อาคาร" + (dr[i]["ICM_CON_BUILDING"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_FLOOR"]).ToString().Length > 0 ? " ชั้น " + (dr[i]["ICM_CON_FLOOR"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_VILLAGE"]).ToString().Length > 0 ? " หมู่บ้าน" + (dr[i]["ICM_CON_VILLAGE"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_ADDRESS"]).ToString().Length > 0 ? " บ้านเลขที่ " + (dr[i]["ICM_CON_ADDRESS"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_ROAD"]).ToString().Length > 0 ? " ถนน" + (dr[i]["ICM_CON_ROAD"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_LANE"]).ToString().Length > 0 ? " ซอย " + (dr[i]["ICM_CON_LANE"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_DISTRICT"]).ToString().Length > 0 ? " แขวง/ตำบล" + (dr[i]["ICM_CON_DISTRICT"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_AMPHUR"]).ToString().Length > 0 ? " เขต/อำเภอ" + (dr[i]["ICM_CON_AMPHUR"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_POST"]).ToString().Length > 0 ? " รหัสไปรษณีย์ " + (dr[i]["ICM_CON_POST"]).ToString() : "");
                        info.All_address += ((dr[i]["ICM_CON_PROVINCE"]).ToString().Length > 0 ? " จังหวัด" + (dr[i]["ICM_CON_PROVINCE"]).ToString() : "");
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<MasterContactInfo> GetMasterContact(string record_status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<MasterContactInfo> infoList = new List<MasterContactInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@record_status", record_status);

                strSQL = "SELECT c.*"
                            + ", g.ICM_CON_GRP_NAME"
                            + ", s.ICM_CON_STAT_NAME"
                            + ", v.ICM_VAT_NAME"
                        + " FROM ICM_MAS_CONTACT c"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP g ON c.ICM_CON_GRP_SEQ = g.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_STATUS s ON c.ICM_CON_STAT_SEQ = s.ICM_CON_STAT_SEQ"
                            + " INNER JOIN ICM_SYS_VAT v ON c.ICM_VAT_SEQ = v.ICM_VAT_SEQ"
                        + " WHERE c.RECORD_STATUS = @record_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        MasterContactInfo info = new MasterContactInfo();
                        SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                        SysContactStatusInfo infoStatus = new SysContactStatusInfo();
                        SysVatInfo infoVat = new SysVatInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_CON_SEQ"]);
                        info.Name_th = (dr[i]["ICM_CON_NAME_TH"]).ToString();
                        info.Name_eng = (dr[i]["ICM_CON_NAME_ENG"]).ToString();
                        infoGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_GRP_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_CON_GRP_SEQ"]));
                        infoGroup.Name = (dr[i]["ICM_CON_GRP_NAME"]).ToString();
                        infoStatus.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_STAT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_CON_STAT_SEQ"]));
                        infoStatus.Name = (dr[i]["ICM_CON_STAT_NAME"]).ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["ICM_VAT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_VAT_SEQ"]));
                        infoVat.Name = (dr[i]["ICM_VAT_NAME"]).ToString();
                        info.Country = (dr[i]["ICM_CON_COUNTRY"]).ToString();
                        info.Tax_code = (dr[i]["ICM_CON_TAX_CODE"]).ToString();
                        info.Gfmis_code = (dr[i]["ICM_CON_GFMIS_CODE"]).ToString();
                        info.Reg_date = (Convert.IsDBNull(dr[i]["ICM_CON_REG_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["ICM_CON_REG_DATE"]));
                        info.Reg_capital = (Convert.IsDBNull(dr[i]["ICM_CON_REG_CAPITAL"]) ? null : (Nullable<double>)(dr[i]["ICM_CON_REG_CAPITAL"]));
                        info.Status_remark = (dr[i]["ICM_CON_STAT_REMARK"]).ToString();
                        info.Coordinator = (dr[i]["ICM_CON_COORDINATOR"]).ToString();
                        info.Building = (dr[i]["ICM_CON_BUILDING"]).ToString();
                        info.Floor = (dr[i]["ICM_CON_FLOOR"]).ToString();
                        info.Village = (dr[i]["ICM_CON_VILLAGE"]).ToString();
                        info.Address = (dr[i]["ICM_CON_ADDRESS"]).ToString();
                        info.Road = (dr[i]["ICM_CON_ROAD"]).ToString();
                        info.Lane = (dr[i]["ICM_CON_LANE"]).ToString();
                        info.District = (dr[i]["ICM_CON_DISTRICT"]).ToString();
                        info.Amphur = (dr[i]["ICM_CON_AMPHUR"]).ToString();
                        info.Post = (dr[i]["ICM_CON_POST"]).ToString();
                        info.Province = (dr[i]["ICM_CON_PROVINCE"]).ToString();
                        info.Tel = (dr[i]["ICM_CON_TEL"]).ToString();
                        info.Tel_next = (dr[i]["ICM_CON_TEL_NEXT"]).ToString();
                        info.Fax = (dr[i]["ICM_CON_FAX"]).ToString();
                        info.Fax_next = (dr[i]["ICM_CON_FAX_NEXT"]).ToString();
                        info.Email = (dr[i]["ICM_CON_EMAIL"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        infoRecordStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoRecord.InfoStatus = infoRecordStatus;
                        info.InfoGroup = infoGroup;
                        info.InfoStatus = infoStatus;
                        info.InfoVat = infoVat;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public MasterContactInfo GetMasterContact(int seq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            MasterContactInfo info = new MasterContactInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT c.*"
                            + ", g.ICM_CON_GRP_NAME"
                            + ", s.ICM_CON_STAT_NAME"
                            + ", v.ICM_VAT_NAME"
                        + " FROM ICM_MAS_CONTACT c"
                            + " INNER JOIN ICM_SYS_CONTACT_GROUP g ON c.ICM_CON_GRP_SEQ = g.ICM_CON_GRP_SEQ"
                            + " INNER JOIN ICM_SYS_CONTACT_STATUS s ON c.ICM_CON_STAT_SEQ = s.ICM_CON_STAT_SEQ"
                            + " INNER JOIN ICM_SYS_VAT v ON c.ICM_VAT_SEQ = v.ICM_VAT_SEQ"  
                        + " WHERE c.ICM_CON_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                    SysContactStatusInfo infoStatus = new SysContactStatusInfo();
                    SysVatInfo infoVat = new SysVatInfo();
                    RecordInfo infoRecord = new RecordInfo();
                    SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                    info.Seq = Convert.ToInt32(dr[0]["ICM_CON_SEQ"]);
                    info.Name_th = (dr[0]["ICM_CON_NAME_TH"]).ToString();
                    info.Name_eng = (dr[0]["ICM_CON_NAME_ENG"]).ToString();
                    infoGroup.Seq = (Convert.IsDBNull(dr[0]["ICM_CON_GRP_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_CON_GRP_SEQ"]));
                    infoGroup.Name = (dr[0]["ICM_CON_GRP_NAME"]).ToString();
                    infoStatus.Seq = (Convert.IsDBNull(dr[0]["ICM_CON_STAT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_CON_STAT_SEQ"]));
                    infoStatus.Name = (dr[0]["ICM_CON_STAT_NAME"]).ToString();
                    infoVat.Seq = (Convert.IsDBNull(dr[0]["ICM_VAT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_VAT_SEQ"]));
                    infoVat.Name = (dr[0]["ICM_VAT_NAME"]).ToString();
                    info.Country = (dr[0]["ICM_CON_COUNTRY"]).ToString();
                    info.Tax_code = (dr[0]["ICM_CON_TAX_CODE"]).ToString();
                    info.Gfmis_code = (dr[0]["ICM_CON_GFMIS_CODE"]).ToString();
                    info.Reg_date = (Convert.IsDBNull(dr[0]["ICM_CON_REG_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["ICM_CON_REG_DATE"]));
                    info.Reg_capital = (Convert.IsDBNull(dr[0]["ICM_CON_REG_CAPITAL"]) ? null : (Nullable<double>)(dr[0]["ICM_CON_REG_CAPITAL"]));
                    info.Status_remark = (dr[0]["ICM_CON_STAT_REMARK"]).ToString();
                    info.Coordinator = (dr[0]["ICM_CON_COORDINATOR"]).ToString();
                    info.Building = (dr[0]["ICM_CON_BUILDING"]).ToString();
                    info.Floor = (dr[0]["ICM_CON_FLOOR"]).ToString();
                    info.Village = (dr[0]["ICM_CON_VILLAGE"]).ToString();
                    info.Address = (dr[0]["ICM_CON_ADDRESS"]).ToString();
                    info.Road = (dr[0]["ICM_CON_ROAD"]).ToString();
                    info.Lane = (dr[0]["ICM_CON_LANE"]).ToString();
                    info.District = (dr[0]["ICM_CON_DISTRICT"]).ToString();
                    info.Amphur = (dr[0]["ICM_CON_AMPHUR"]).ToString();
                    info.Post = (dr[0]["ICM_CON_POST"]).ToString();
                    info.Province = (dr[0]["ICM_CON_PROVINCE"]).ToString();
                    info.Tel = (dr[0]["ICM_CON_TEL"]).ToString();
                    info.Tel_next = (dr[0]["ICM_CON_TEL_NEXT"]).ToString();
                    info.Fax = (dr[0]["ICM_CON_FAX"]).ToString();
                    info.Fax_next = (dr[0]["ICM_CON_FAX_NEXT"]).ToString();
                    info.Email = (dr[0]["ICM_CON_EMAIL"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    infoRecordStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    infoRecord.InfoStatus = infoRecordStatus;
                    info.InfoGroup = infoGroup;
                    info.InfoStatus = infoStatus;
                    info.InfoVat = infoVat;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(MasterContactInfo info)
        {
            string strSQL;
            int lastSeq = 0;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(30);

                dbManager.AddParameters(0, "@name_th", info.Name_th);
                dbManager.AddParameters(1, "@name_eng", info.Name_eng);
                dbManager.AddParameters(2, "@group_seq", info.InfoGroup.Seq);
                dbManager.AddParameters(3, "@country", info.Country);
                dbManager.AddParameters(4, "@tax_code", info.Tax_code);
                dbManager.AddParameters(5, "@gfmis_code", info.Gfmis_code);
                dbManager.AddParameters(6, "@reg_date", info.Reg_date);
                dbManager.AddParameters(7, "@reg_capital", info.Reg_capital);
                dbManager.AddParameters(8, "@status_seq", info.InfoStatus.Seq);
                dbManager.AddParameters(9, "@status_remark", info.Status_remark);
                dbManager.AddParameters(10, "@coordinator", info.Coordinator);
                dbManager.AddParameters(11, "@vat_seq", info.InfoVat.Seq);
                dbManager.AddParameters(12, "@building", info.Building);
                dbManager.AddParameters(13, "@floor", info.Floor);
                dbManager.AddParameters(14, "@village", info.Village);
                dbManager.AddParameters(15, "@address", info.Address);
                dbManager.AddParameters(16, "@road", info.Road);
                dbManager.AddParameters(17, "@lane", info.Lane);
                dbManager.AddParameters(18, "@district", info.District);
                dbManager.AddParameters(19, "@amphur", info.Amphur);
                dbManager.AddParameters(20, "@post", info.Post);
                dbManager.AddParameters(21, "@province", info.Province);
                dbManager.AddParameters(22, "@tel", info.Tel);
                dbManager.AddParameters(23, "@tel_next", info.Tel_next);
                dbManager.AddParameters(24, "@fax", info.Fax);
                dbManager.AddParameters(25, "@fax_next", info.Fax_next);
                dbManager.AddParameters(26, "@email", info.Email);
                dbManager.AddParameters(27, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(28, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(29, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_MAS_CONTACT"
                    + " (ICM_CON_NAME_TH, ICM_CON_NAME_ENG, ICM_CON_GRP_SEQ, ICM_CON_COUNTRY, ICM_CON_TAX_CODE, ICM_CON_GFMIS_CODE"
                    + ", ICM_CON_REG_DATE, ICM_CON_REG_CAPITAL, ICM_CON_STAT_SEQ, ICM_CON_STAT_REMARK, ICM_CON_COORDINATOR"
                    + ", ICM_CON_VAT_SEQ, ICM_CON_BUILDING, ICM_CON_FLOOR, ICM_CON_VILLAGE, ICM_CON_ADDRESS, ICM_CON_ROAD, ICM_CON_LANE"
                    + ", ICM_CON_DISTRICT, ICM_CON_AMPHUR, ICM_CON_POST, ICM_CON_PROVINCE, ICM_CON_TEL, ICM_CON_TEL_NEXT"
                    + ", ICM_CON_FAX, ICM_CON_FAX_NEXT, ICM_CON_EMAIL"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@name_th"
                    + ", @name_eng"
                    + ", @group_seq"
                    + ", @country"
                    + ", @tax_code"
                    + ", @gfmis_code"
                    + ", @reg_date"
                    + ", @reg_capital"
                    + ", @status_seq"
                    + ", @status_remark"
                    + ", @coordinator"
                    + ", @vat_seq"
                    + ", @building"
                    + ", @floor"
                    + ", @village"
                    + ", @address"
                    + ", @road"
                    + ", @lane"
                    + ", @district"
                    + ", @amphur"
                    + ", @post"
                    + ", @province"
                    + ", @tel"
                    + ", @tel_next"
                    + ", @fax"
                    + ", @fax_next"
                    + ", @email"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_MAS_CONTACT";
                DataSet ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                InsertFimMasContact(dbManager, lastSeq.ToString());
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(MasterContactInfo info, IList<MasterContactBankInfo> infoListBank, IList<MasterContactPartnerInfo> infoListPartner)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            MasterContactBankDAL dalBank = new MasterContactBankDAL();
            MasterContactPartnerDAL dalPartner = new MasterContactPartnerDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(30);

                dbManager.AddParameters(0, "@name_th", info.Name_th);
                dbManager.AddParameters(1, "@name_eng", info.Name_eng);
                dbManager.AddParameters(2, "@group_seq", info.InfoGroup.Seq);
                dbManager.AddParameters(3, "@country", info.Country);
                dbManager.AddParameters(4, "@tax_code", info.Tax_code);
                dbManager.AddParameters(5, "@gfmis_code", info.Gfmis_code);
                dbManager.AddParameters(6, "@reg_date", info.Reg_date);
                dbManager.AddParameters(7, "@reg_capital", info.Reg_capital);
                dbManager.AddParameters(8, "@status_seq", info.InfoStatus.Seq);
                dbManager.AddParameters(9, "@status_remark", info.Status_remark);
                dbManager.AddParameters(10, "@coordinator", info.Coordinator);
                dbManager.AddParameters(11, "@vat_seq", info.InfoVat.Seq);
                dbManager.AddParameters(12, "@building", info.Building);
                dbManager.AddParameters(13, "@floor", info.Floor);
                dbManager.AddParameters(14, "@village", info.Village);
                dbManager.AddParameters(15, "@address", info.Address);
                dbManager.AddParameters(16, "@road", info.Road);
                dbManager.AddParameters(17, "@lane", info.Lane);
                dbManager.AddParameters(18, "@district", info.District);
                dbManager.AddParameters(19, "@amphur", info.Amphur);
                dbManager.AddParameters(20, "@post", info.Post);
                dbManager.AddParameters(21, "@province", info.Province);
                dbManager.AddParameters(22, "@tel", info.Tel);
                dbManager.AddParameters(23, "@tel_next", info.Tel_next);
                dbManager.AddParameters(24, "@fax", info.Fax);
                dbManager.AddParameters(25, "@fax_next", info.Fax_next);
                dbManager.AddParameters(26, "@email", info.Email);
                dbManager.AddParameters(27, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(28, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(29, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_MAS_CONTACT"
                    + " (ICM_CON_NAME_TH, ICM_CON_NAME_ENG, ICM_CON_GRP_SEQ, ICM_CON_COUNTRY, ICM_CON_TAX_CODE, ICM_CON_GFMIS_CODE"
                    + ", ICM_CON_REG_DATE, ICM_CON_REG_CAPITAL, ICM_CON_STAT_SEQ, ICM_CON_STAT_REMARK, ICM_CON_COORDINATOR"
                    + ", ICM_VAT_SEQ, ICM_CON_BUILDING, ICM_CON_FLOOR, ICM_CON_VILLAGE, ICM_CON_ADDRESS, ICM_CON_ROAD, ICM_CON_LANE"
                    + ", ICM_CON_DISTRICT, ICM_CON_AMPHUR, ICM_CON_POST, ICM_CON_PROVINCE, ICM_CON_TEL, ICM_CON_TEL_NEXT"
                    + ", ICM_CON_FAX, ICM_CON_FAX_NEXT, ICM_CON_EMAIL"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@name_th"
                    + ", @name_eng"
                    + ", @group_seq"
                    + ", @country"
                    + ", @tax_code"
                    + ", @gfmis_code"
                    + ", @reg_date"
                    + ", @reg_capital"
                    + ", @status_seq"
                    + ", @status_remark"
                    + ", @coordinator"
                    + ", @vat_seq"
                    + ", @building"
                    + ", @floor"
                    + ", @village"
                    + ", @address"
                    + ", @road"
                    + ", @lane"
                    + ", @district"
                    + ", @amphur"
                    + ", @post"
                    + ", @province"
                    + ", @tel"
                    + ", @tel_next"
                    + ", @fax"
                    + ", @fax_next"
                    + ", @email"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_MAS_CONTACT";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                dalBank.Insert(infoListBank, lastSeq, dbManager);
                dalPartner.Insert(infoListPartner, lastSeq, dbManager);
                InsertFimMasContact(dbManager, lastSeq.ToString());

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        protected void InsertFimMasContact(IDBManager dbManager, string icm_con_seq)
        {
            string strSQL;
            try
            {
                string contact_type = dbManager.Parameters[2].Value.ToString();
                string is_juristic = "";

                if (contact_type == "1")
                {
                    contact_type = "4";
                    is_juristic = "0";
                }
                else if (contact_type == "2")
                {
                    contact_type = "3";
                    is_juristic = "1";
                }
                else if (contact_type == "3")
                {
                    contact_type = "1";
                    is_juristic = "1";
                }
                else if (contact_type == "4")
                {
                    contact_type = "3";
                    is_juristic = "1";
                }

                strSQL = "INSERT INTO FIM_MAS_CONTACTS"
                    + " (contact_name, seller_seq, contact_type, contact_gfmis_code"
                    + ", tax_id, citizen_id, is_juristic, tax_deduct, address, district_seq, country_seq"
                    + ", postal_code, tel, fax, email, contact_status, record_status, created_by, created_when)"
                    + " VALUES(@name_th"
                    + ", " + icm_con_seq
                    + ", '" + contact_type + "'"
                    + ", @gfmis_code"
                    + ", @tax_code"
                    + ", null"
                    + ", '" + is_juristic + "'"
                    + ", @vat_seq"
                    + ", @address"
                    + ", null"
                    + ", null"
                    + ", @post"
                    + ", @tel"
                    + ", @fax"
                    + ", @email"
                    + ", 'A'"
                    + ", 'U'"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
            }
        }

        public void Update(MasterContactInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(31);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@name_th", info.Name_th);
                dbManager.AddParameters(2, "@name_eng", info.Name_eng);
                dbManager.AddParameters(3, "@group_seq", info.InfoGroup.Seq);
                dbManager.AddParameters(4, "@country", info.Country);
                dbManager.AddParameters(5, "@tax_code", info.Tax_code);
                dbManager.AddParameters(6, "@gfmis_code", info.Gfmis_code);
                dbManager.AddParameters(7, "@reg_date", info.Reg_date);
                dbManager.AddParameters(8, "@reg_capital", info.Reg_capital);
                dbManager.AddParameters(9, "@status_seq", info.InfoStatus.Seq);
                dbManager.AddParameters(10, "@status_remark", info.Status_remark);
                dbManager.AddParameters(11, "@coordinator", info.Coordinator);
                dbManager.AddParameters(12, "@vat_seq", info.InfoVat.Seq);
                dbManager.AddParameters(13, "@building", info.Building);
                dbManager.AddParameters(14, "@floor", info.Floor);
                dbManager.AddParameters(15, "@village", info.Village);
                dbManager.AddParameters(16, "@address", info.Address);
                dbManager.AddParameters(17, "@road", info.Road);
                dbManager.AddParameters(18, "@lane", info.Lane);
                dbManager.AddParameters(19, "@district", info.District);
                dbManager.AddParameters(20, "@amphur", info.Amphur);
                dbManager.AddParameters(21, "@post", info.Post);
                dbManager.AddParameters(22, "@province", info.Province);
                dbManager.AddParameters(23, "@tel", info.Tel);
                dbManager.AddParameters(24, "@tel_next", info.Tel_next);
                dbManager.AddParameters(25, "@fax", info.Fax);
                dbManager.AddParameters(26, "@fax_next", info.Fax_next);
                dbManager.AddParameters(27, "@email", info.Email);
                dbManager.AddParameters(28, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(29, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(30, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_MAS_CONTACT"
                    + " SET RECORD_STATUS = @record_status"
                    + (info.Name_th != null ? ", ICM_CON_NAME_TH = @name_th" : "")
                    + (info.Name_eng != null ? ", ICM_CON_NAME_ENG = @name_eng" : "")
                    + (info.InfoGroup.Seq != null ? ", ICM_CON_GRP_SEQ = @group_seq" : "")
                    + (info.Country != null ? ", ICM_CON_COUNTRY = @country" : "")
                    + (info.Tax_code != null ? ", ICM_CON_TAX_CODE = @tax_code" : "")
                    + (info.Gfmis_code != null ? ", ICM_CON_GFMIS_CODE = @gfmis_code" : "")
                    + (info.Reg_date != null ? ", ICM_CON_REG_DATE = @reg_date" : "")
                    + (info.Reg_capital != null ? ", ICM_CON_REG_CAPITAL = @reg_capital" : "")
                    + (info.InfoStatus.Seq != null ? ", ICM_CON_STAT_SEQ = @status_seq" : "")
                    + (info.Status_remark != null ? ", ICM_CON_STAT_REMARK = @status_remark" : "")
                    + (info.Coordinator != null ? ", ICM_CON_COORDINATOR = @coordinator" : "")
                    + (info.InfoVat.Seq != null ? ", ICM_VAT_SEQ = @vat_seq" : "")
                    + (info.Building != null ? ", ICM_CON_BUILDING = @building" : "")
                    + (info.Floor != null ? ", ICM_CON_FLOOR = @floor" : "")
                    + (info.Village != null ? ", ICM_CON_VILLAGE = @village" : "")
                    + (info.Address != null ? ", ICM_CON_ADDRESS = @address" : "")
                    + (info.Road != null ? ", ICM_CON_ROAD = @road" : "")
                    + (info.Lane != null ? ", ICM_CON_LANE = @lane" : "")
                    + (info.District != null ? ", ICM_CON_DISTRICT = @district" : "")
                    + (info.Amphur != null ? ", ICM_CON_AMPHUR = @amphur" : "")
                    + (info.Post != null ? ", ICM_CON_POST = @post" : "")
                    + (info.Province != null ? ", ICM_CON_PROVINCE = @province" : "")
                    + (info.Tel != null ? ", ICM_CON_TEL = @tel" : "")
                    + (info.Tel_next != null ? ", ICM_CON_TEL_NEXT = @tel_next" : "")
                    + (info.Fax != null ? ", ICM_CON_FAX = @fax" : "")
                    + (info.Fax_next != null ? ", ICM_CON_FAX_NEXT = @fax_next" : "")
                    + (info.Email != null ? ", ICM_CON_EMAIL = @email" : "")
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_CON_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(MasterContactInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_MAS_CONTACT"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_CON_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<MasterContactInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_MAS_CONTACT"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_CON_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<MasterContactInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_MAS_CONTACT"
                    + " WHERE ICM_CON_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Restore(IList<MasterContactInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(1, "@restored_by", infoList[0].InfoRecord.Restored_by);
                dbManager.AddParameters(2, "@restored_when", infoList[0].InfoRecord.Restored_when);

                strSQL = "UPDATE ICM_MAS_CONTACT"
                    + " SET RECORD_STATUS = @record_status"
                    + ", RESTORED_BY = @restored_by"
                    + ", RESTORED_WHEN = @restored_when"
                    + " WHERE ICM_CON_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<MasterContactInfo> GetMasterContactNoBlackList(string search_text)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<MasterContactInfo> infoList = new List<MasterContactInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@search_text", search_text);

                strSQL = "SELECT c.icm_con_seq " +
                            " ,concat(c.icm_con_name_th, ' ',case when Length(c.icm_con_name_eng) > 0 then concat('(',c.icm_con_name_eng,')') else ' ' end) as icm_con_name " +
                            " ,cg.icm_con_grp_seq " +
                            " ,cg.icm_con_grp_name " +
                            " ,c.icm_con_gfmis_code " +
                            " ,c.icm_con_address " +
                            " ,c.ICM_CON_STAT_SEQ " +
                            " ,cs.icm_con_stat_name " +
                            " ,c.icm_vat_seq" +
                        " FROM icm_mas_contact c" +
                            " Inner Join icm_sys_contact_group cg ON c.icm_con_grp_seq = cg.icm_con_grp_seq " +
                            " Inner Join icm_sys_contact_status cs ON c.icm_con_stat_seq = cs.icm_con_stat_seq  " +
                        " WHERE cs.icm_con_stat_name <> 'black list'"
                        + " AND CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (cg.icm_con_grp_name LIKE CONCAT('%', @search_text , '%')"
                                    + " OR c.icm_con_name_th LIKE CONCAT('%', @search_text , '%')"
                                    + " OR c.icm_con_name_eng LIKE CONCAT('%', @search_text , '%')"
                                    + " OR c.icm_con_gfmis_code LIKE CONCAT('%', @search_text , '%')"
                                    + " OR c.icm_con_address LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                        + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        MasterContactInfo info = new MasterContactInfo();
                        SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                        SysContactStatusInfo infoStatus = new SysContactStatusInfo();
                        SysVatInfo infoVat = new SysVatInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_CON_SEQ"]);
                        info.Name_th = (dr[i]["icm_con_name"]).ToString();
                        infoGroup.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_GRP_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_CON_GRP_SEQ"]));
                        infoGroup.Name = (dr[i]["ICM_CON_GRP_NAME"]).ToString();
                        info.Gfmis_code = (dr[i]["ICM_CON_GFMIS_CODE"]).ToString();
                        info.Address = (dr[i]["ICM_CON_ADDRESS"]).ToString();
                        infoStatus.Seq = (Convert.IsDBNull(dr[i]["ICM_CON_STAT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_CON_STAT_SEQ"]));
                        infoStatus.Name = (dr[i]["ICM_CON_STAT_NAME"]).ToString();
                        infoVat.Seq = (Convert.IsDBNull(dr[i]["ICM_VAT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_VAT_SEQ"]));
                        
                        info.InfoGroup = infoGroup;
                        info.InfoStatus = infoStatus;
                        info.InfoVat = infoVat;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public MasterContactInfo GetContactByPo(int PoSeq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            MasterContactInfo info = new MasterContactInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_po_seq", PoSeq);

                strSQL = "select c.* " +
                            " , cg.icm_con_grp_name  " +
                        " from icm_bidder b " +
                            " inner join icm_purchase_order po on po.icm_bidder_seq = b.icm_bidder_seq" + 
                            " inner join icm_mas_contact c on c.icm_con_seq = b.icm_con_seq " +
                            " inner join icm_sys_contact_group cg on c.icm_con_grp_seq = cg.icm_con_grp_seq " +
                        " where po.icm_po_seq = @icm_po_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    SysContactGroupInfo infoGroup = new SysContactGroupInfo();
                    info.Seq = Convert.ToInt32(dr[0]["ICM_CON_SEQ"]);
                    info.Gfmis_code = (dr[0]["ICM_CON_GFMIS_CODE"]).ToString();
                    infoGroup.Seq = (Convert.IsDBNull(dr[0]["ICM_CON_GRP_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_CON_GRP_SEQ"]));
                    infoGroup.Name = (dr[0]["ICM_CON_GRP_NAME"]).ToString();
                    info.Name_th = (dr[0]["ICM_CON_NAME_TH"]).ToString();
                    info.Name_eng = (dr[0]["ICM_CON_NAME_ENG"]).ToString();
                    info.All_address += ((dr[0]["ICM_CON_COUNTRY"]).ToString().Length > 0 ? " ประเทศ" + (dr[0]["ICM_CON_COUNTRY"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_BUILDING"]).ToString().Length > 0 ? " อาคาร" + (dr[0]["ICM_CON_BUILDING"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_FLOOR"]).ToString().Length > 0 ? " ชั้น " + (dr[0]["ICM_CON_FLOOR"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_VILLAGE"]).ToString().Length > 0 ? " หมู่บ้าน" + (dr[0]["ICM_CON_VILLAGE"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_ADDRESS"]).ToString().Length > 0 ? " บ้านเลขที่ " + (dr[0]["ICM_CON_ADDRESS"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_ROAD"]).ToString().Length > 0 ? " ถนน" + (dr[0]["ICM_CON_ROAD"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_LANE"]).ToString().Length > 0 ? " ซอย " + (dr[0]["ICM_CON_LANE"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_DISTRICT"]).ToString().Length > 0 ? " แขวง/ตำบล" + (dr[0]["ICM_CON_DISTRICT"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_AMPHUR"]).ToString().Length > 0 ? " เขต/อำเภอ" + (dr[0]["ICM_CON_AMPHUR"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_POST"]).ToString().Length > 0 ? " รหัสไปรษณีย์ " + (dr[0]["ICM_CON_POST"]).ToString() : "");
                    info.All_address += ((dr[0]["ICM_CON_PROVINCE"]).ToString().Length > 0 ? " จังหวัด" + (dr[0]["ICM_CON_PROVINCE"]).ToString() : "");
                    info.Tel = (dr[0]["ICM_CON_TEL"]).ToString();
                    info.Email = (dr[0]["ICM_CON_EMAIL"]).ToString();

                    info.InfoGroup = infoGroup;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
    }
}
